package com.powernode.ssm.service;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * SQL生成器（支持MySQL/Oracle/PostgreSQL）
 */
@Slf4j
@Service
public class SqlGeneratorService {

    /**
     * 生成表结构DDL语句
     */
    public String generateCreateDdl(DatabaseMetaDataService.ColumnInfo[] columns, String dbType, String tableName) {
        StringBuilder ddl = new StringBuilder();
        ddl.append("CREATE TABLE ").append(wrapIdentifier(tableName, dbType)).append(" (\n");

        // 处理字段定义
        for (int i = 0; i < columns.length; i++) {
            DatabaseMetaDataService.ColumnInfo col = columns[i];
            ddl.append("    ").append(wrapIdentifier(col.getName(), dbType))
                    .append(" ").append(mapDbType(col.getType(), dbType, col.getSize()));

            if (!col.isNullable()) {
                ddl.append(" NOT NULL");
            }
            if (i < columns.length - 1) {
                ddl.append(",");
            }
            ddl.append("\n");
        }

        // 处理主键
        List<DatabaseMetaDataService.ColumnInfo> pks = new ArrayList<>();
        for (DatabaseMetaDataService.ColumnInfo column : columns) {
            if (column.isPrimaryKey()) {
                pks.add(column);
            }
        }

        if (!pks.isEmpty()) {
            ddl.append("    ,PRIMARY KEY (");
            for (int i = 0; i < pks.size(); i++) {
                ddl.append(wrapIdentifier(pks.get(i).getName(), dbType));
                if (i < pks.size() - 1) {
                    ddl.append(", ");
                }
            }
            ddl.append(")\n");
        }
        ddl.append(");");
        return ddl.toString();
    }

    /**
     * 生成插入数据DML语句（支持批量插入优化）
     */
    public String generateInsertDml(String tableName, List<Object[]> dataList,
                                    DatabaseMetaDataService.ColumnInfo[] columns, String dbType) {
        if (dataList == null || dataList.isEmpty()) {
            return "";
        }

        // ✅ 控制每批插入的最大行数，避免单条SQL过大（比如MySQL的max_allowed_packet限制）
        int maxRowsPerInsert = 500; // 可根据实际情况调整：300 ~ 1000
        StringBuilder dml = new StringBuilder();

        dml.append("INSERT INTO ").append(wrapIdentifier(tableName, dbType)).append(" (");

        // 字段列表（只拼接一次）
        for (int i = 0; i < columns.length; i++) {
            dml.append(wrapIdentifier(columns[i].getName(), dbType));
            if (i < columns.length - 1) {
                dml.append(", ");
            }
        }
        dml.append(") VALUES ");

        // -------------------------------
        // ✅ 按 maxRowsPerInsert 切分行，分批拼接 VALUES 部分
        // -------------------------------
        int totalRows = dataList.size();
        for (int start = 0; start < totalRows; start += maxRowsPerInsert) {
            int end = Math.min(start + maxRowsPerInsert, totalRows);
            List<Object[]> batch = dataList.subList(start, end);

            if (start > 0) {
                dml.append(","); // 不是第一批次，前面加逗号
            }

            // 拼接当前批次的 VALUES
            for (int rowIdx = 0; rowIdx < batch.size(); rowIdx++) {
                Object[] values = batch.get(rowIdx);
                dml.append("(");
                for (int colIdx = 0; colIdx < values.length; colIdx++) {
                    dml.append(convertValue(values[colIdx], dbType));
                    if (colIdx < values.length - 1) {
                        dml.append(", ");
                    }
                }
                dml.append(")");
                if (rowIdx < batch.size() - 1) {
                    dml.append(", ");
                }
            }

            // 每个批次结束后换行（可选，提高可读性）
            dml.append("\n");
        }

        dml.append(";");
        return dml.toString();
    }

    private String wrapIdentifier(String identifier, String dbType) {
        switch (dbType.toLowerCase()) {
            case "mysql": return "`" + identifier + "`";
            case "oracle":
            case "postgresql": return "\"" + identifier + "\"";
            default: return identifier;
        }
    }

    private String mapDbType(String dbType, String targetDbType, int size) {
        if (StrUtil.isBlank(dbType)) {
            return "VARCHAR(255)"; // 默认类型，防止NPE
        }

        String normalizedDbType = dbType.toLowerCase().trim();
        targetDbType = targetDbType.toLowerCase();

        switch (targetDbType) {
            case "mysql":
                return mapToMySqlType(normalizedDbType, size);
            case "oracle":
                return mapToOracleType(normalizedDbType, size);
            case "postgresql":
                return mapToPostgreSqlType(normalizedDbType, size);
            default:
                return dbType; // 未知数据库类型，原样返回
        }
    }

    // ========== MySQL 类型映射 ==========
    private String mapToMySqlType(String dbType, int size) {
        switch (dbType) {
            case "int":
            case "integer":
                return "INT";
            case "bigint":
                return "BIGINT";
            case "smallint":
                return "SMALLINT";
            case "tinyint":
                return "TINYINT";
            case "varchar":
            case "char":
                return StrUtil.format("VARCHAR({})", size > 0 ? size : 255);
            case "text":
                return "TEXT";
            case "longtext":
                return "LONGTEXT";
            case "mediumtext":
                return "MEDIUMTEXT";
            case "datetime":
                return "DATETIME";
            case "timestamp":
                return "TIMESTAMP";
            case "date":
                return "DATE";
            case "time":
                return "TIME";
            case "decimal":
            case "numeric":
                if (size > 0) {
                    int scale = 2; // 默认小数位
                    return StrUtil.format("DECIMAL({}, {})", size, scale);
                }
                return "DECIMAL(10,2)";
            case "float":
                return "FLOAT";
            case "double":
                return "DOUBLE";
            case "boolean":
            case "bit":
                return "TINYINT(1)"; // MySQL常用布尔模拟
            case "blob":
                return "BLOB";
            case "longblob":
                return "LONGBLOB";
            case "json":
                return "JSON"; // MySQL 5.7+
            case "clob":
                return "TEXT";
            default:
                return dbType; // 未知类型原样返回
        }
    }

    // ========== Oracle 类型映射 ==========
    private String mapToOracleType(String dbType, int size) {
        switch (dbType) {
            case "int":
            case "integer":
                return size <= 10 ? "NUMBER(10)" : StrUtil.format("NUMBER({})", size);
            case "bigint":
                return "NUMBER(19)";
            case "smallint":
                return "NUMBER(5)";
            case "tinyint":
                return "NUMBER(3)";
            case "varchar":
            case "varchar2":
                return StrUtil.format("VARCHAR2({})", size > 0 ? size : 255);
            case "char":
                return StrUtil.format("CHAR({})", size > 0 ? size : 1);
            case "text":
            case "clob":
                return "CLOB";
            case "longtext":
                return "CLOB";
            case "datetime":
            case "date":
                return "DATE";
            case "timestamp":
                return "TIMESTAMP";
            case "timestamp with time zone":
                return "TIMESTAMP WITH TIME ZONE";
            case "decimal":
            case "numeric":
                if (size > 0) {
                    int scale = 2;
                    return StrUtil.format("NUMBER({}, {})", size, scale);
                }
                return "NUMBER(10,2)";
            case "float":
                return "BINARY_FLOAT";
            case "double":
                return "BINARY_DOUBLE";
            case "boolean":
                return "NUMBER(1)"; // 1=true, 0=false
            case "blob":
                return "BLOB";
            case "json":
                return "CLOB"; // Oracle 21c+ 支持 JSON 类型，这里简化为 CLOB
            default:
                return dbType;
        }
    }

    // ========== PostgreSQL 类型映射 ==========
    private String mapToPostgreSqlType(String dbType, int size) {
        switch (dbType) {
            case "int":
            case "integer":
                return "INTEGER";
            case "bigint":
                return "BIGINT";
            case "smallint":
                return "SMALLINT";
            case "tinyint":
                return "SMALLINT";
            case "varchar":
            case "character varying":
                return StrUtil.format("VARCHAR({})", size > 0 ? size : 255);
            case "char":
                return StrUtil.format("CHAR({})", size > 0 ? size : 1);
            case "text":
                return "TEXT";
            case "datetime":
            case "timestamp without time zone":
                return "TIMESTAMP";
            case "timestamp with time zone":
                return "TIMESTAMPTZ";
            case "date":
                return "DATE";
            case "time":
                return "TIME";
            case "decimal":
            case "numeric":
                if (size > 0) {
                    int scale = 2;
                    return StrUtil.format("NUMERIC({}, {})", size, scale);
                }
                return "NUMERIC(10,2)";
            case "float":
                return "REAL";
            case "double":
                return "DOUBLE PRECISION";
            case "boolean":
                return "BOOLEAN";
            case "blob":
                return "BYTEA";
            case "clob":
                return "TEXT";
            case "json":
                return "JSONB"; // PostgreSQL 强大的 JSONB 类型
            case "longtext":
                return "TEXT";
            default:
                return dbType;
        }
    }


    private String convertValue(Object value, String dbType) {
        if (value == null) {
            return "NULL";
        }

        String normalizedDbType = dbType.toLowerCase();

        switch (normalizedDbType) {
            case "mysql":
                if (value instanceof Date) {
                    return "'" + DateUtil.format((Date) value, DatePattern.NORM_DATETIME_PATTERN) + "'";
                } else if (value instanceof Boolean) {
                    return ((Boolean) value) ? "1" : "0";
                } else if (value instanceof String) {
                    return "'" + ((String) value).replace("'", "''") + "'";
                } else if (value instanceof Number) {
                    return value.toString();
                } else if (value instanceof byte[]) {
                    return "NULL"; // BLOB 不建议直接插入字面值
                }
                break;

            case "oracle":
                if (value instanceof Date) {
                    return "TO_DATE('" + DateUtil.format((Date) value, DatePattern.NORM_DATETIME_PATTERN) + "', 'YYYY-MM-DD HH24:MI:SS')";
                } else if (value instanceof Boolean) {
                    return ((Boolean) value) ? "1" : "0";
                } else if (value instanceof String) {
                    return "'" + ((String) value).replace("'", "''") + "'";
                } else if (value instanceof Number) {
                    return value.toString();
                } else if (value instanceof byte[]) {
                    return "NULL"; // BLOB
                }
                break;

            case "postgresql":
                if (value instanceof Date) {
                    return "'" + DateUtil.format((Date) value, DatePattern.NORM_DATETIME_PATTERN) + "'";
                } else if (value instanceof Boolean) {
                    return ((Boolean) value) ? "true" : "false";
                } else if (value instanceof String) {
                    return "'" + ((String) value).replace("'", "''") + "'";
                } else if (value instanceof Number) {
                    return value.toString();
                } else if (value instanceof byte[]) {
                    return "NULL"; // BYTEA
                }
                break;
        }

        // fallback：调用 toString 并包裹（仅对非危险类型建议，生产环境慎用）
        return "'" + value.toString().replace("'", "''") + "'";
    }

}
